package org.baojie.server.refactor.visit;

import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.alter.AlterExpression;
import net.sf.jsqlparser.statement.alter.AlterOperation;
import net.sf.jsqlparser.statement.comment.Comment;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import org.baojie.server.refactor.CareSentence;
import org.baojie.server.refactor.DataBaseType;
import org.baojie.server.refactor.ParserState;
import org.baojie.server.refactor.SQLErrorType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @author baojie
 * @desc 功能描述
 * @date 2022/6/17 上午11:17
 */
public abstract class GomeVisit implements Visitor {

    protected final Logger log = LoggerFactory.getLogger(getClass());

    protected final ParserState state;

    public GomeVisit(ParserState state) {
        if (null == state) {
            throw new NullPointerException();
        }
        this.state = state;
    }

    @Override
    public List<Statement> aboutHukingAlter() {
        List<Statement> alters = state.getCare(CareSentence.ALTER);
        if (null == alters) {
            return Collections.emptyList();
        } else {
            int size = alters.size();
            if (size <= 0) {
                return Collections.emptyList();
            } else {
                return alters;
            }
        }
    }

    @Override
    public boolean onlySelect() {
        final Statements copy = state.getStates();
        if (null == copy) {
            return false;
        } else {
            List<Statement> states = copy.getStatements();
            if (null == states) {
                return false;
            } else {
                int size = states.size();
                if (size <= 0) {
                    return false;
                } else {
                    return select0(size, states);
                }
            }
        }
    }

    private boolean select0(int size, List<Statement> states) {
        for (int i = 0; i < size; i++) {
            Statement state = states.get(i);
            if (null == state) {
                continue;
            }
            if (state instanceof Select) {
                continue;
            } else {
                return false;
            }
        }
        return true;
    }

    /**
     * @return 如果存在没有备注信息的建表语句那么将当前语句返回
     */
    @Override
    public Map<SQLErrorType, ColumnDefinition> visitCreate() {
        List<Statement> creates = state.getCare(CareSentence.CREATE);
        if (null == creates) {
            return Collections.emptyMap();
        } else {
            int size = creates.size();
            if (size <= 0) {
                return Collections.emptyMap();
            } else {
                return visitCreate0(size, creates);
            }
        }
    }

    private Map<SQLErrorType, ColumnDefinition> visitCreate0(int size, List<Statement> creates) {
        final Map<SQLErrorType, ColumnDefinition> error = new ConcurrentHashMap<>(8);
        for (int i = 0; i < size; i++) {
            CreateTable create = (CreateTable) creates.get(i);
            // 过滤 create as 或者 create like 语句
            if (likeOrAs(create)) {
                continue;
            }
            // 获取 create 语句中的字段
            List<ColumnDefinition> definitions = create.getColumnDefinitions();
            if (null == definitions || definitions.size() <= 0) {
                continue;
            }
            // 检测 create 语句中是否包含 primary 主键
            if (!createHasPrimary(create)) {
                final ColumnDefinition columnDefinition = createByTableName(create);
                error.putIfAbsent(SQLErrorType.NO_PRIMARY, columnDefinition);
                return error;
            }
            ColumnDefinition remain = noCommentColumn(create);
            if (null != remain) {
                error.putIfAbsent(SQLErrorType.NO_COMMENT, remain);
                return error;
            }
        }
        return Collections.emptyMap();
    }

    private ColumnDefinition createByTableName(CreateTable create) {
        final ColumnDefinition columnDefinition = new ColumnDefinition();
        Table table;
        if (null != (table = create.getTable()) && null != table.getName()) {
            columnDefinition.setColumnName(table.getName());
        }
        return columnDefinition;
    }

    // 返回没有备注信息的字段
    private ColumnDefinition noCommentColumn(CreateTable create) {
        DataBaseType dbType = state.dbType();
        if (DataBaseType.MySql == dbType) {
            return noCommentColumnInMySQL(create);
        } else if (DataBaseType.Oracle == dbType) {
            return noCommentColumnInOracle(create);
        } else {
            return null;
        }
    }

    private ColumnDefinition noCommentColumnInOracle(CreateTable create) {
        List<ColumnDefinition> definitions = create.getColumnDefinitions();
        List<Statement> comments = state.getCare(CareSentence.COMMENT);
        // oracle 中没有添加备注 comment 的语句
        // 则返回当前的第一条字段信息
        if (null == comments || comments.size() <= 0) {
            return definitions.get(0);
        }
        // 对元数据进行拷贝 copy
        final List<ColumnDefinition> copy = new ArrayList<>(definitions.size());
        copy.addAll(definitions);
        for (int i = comments.size() - 1; i >= 0; i--) {
            Comment comment = (Comment) comments.get(i);
            // 当前 comment 语句必须对应当前表
            if (!isSame(comment, create)) {
                continue;
            }
            // 检索备注字段的名称
            final String cname = columnNameFromComment(comment);
            if (null == cname) {
                continue;
            }
            removeByColumnName(copy, cname);
        }
        if (copy.size() > 0) {
            return copy.get(0);
        } else {
            return null;
        }
    }

    private void removeByColumnName(List<ColumnDefinition> copy, String cname) {
        Iterator<ColumnDefinition> iterator = copy.listIterator();
        while (iterator.hasNext()) {
            ColumnDefinition definition = iterator.next();
            String cn = definition.getColumnName();
            if (null == cn) {
                continue;
            }
            cn = cn.trim().toLowerCase();
            if (cn.equals(cname)) {
                iterator.remove();
            }
        }
    }

    private String columnNameFromComment(Comment comment) {
        Column column = comment.getColumn();
        if (null == column) {
            return null;
        }
        String cname = column.getColumnName();
        if (null == cname) {
            return null;
        }
        cname = cname.trim().toLowerCase();
        if (cname.length() <= 0) {
            return null;
        }
        StringValue value = comment.getComment();
        if (null == value) {
            return null;
        }
        String real = value.getValue();
        if (null == real) {
            return null;
        }
        if (!valueHasReal(real)) {
            return null;
        }
        return cname;
    }

    private ColumnDefinition noCommentColumnInMySQL(CreateTable create) {
        // 先检索 create 语句
        List<ColumnDefinition> noComments = noCommentInDefinitions(create);
        if (noComments.size() > 0) {
            // 如果语句中存在没有注释的字段
            // 再检索 alter 语句
            // 因为用户可能使用 alter 语句添加
            List<Alter> alters = altersAboutCreate(create);
            if (alters.size() <= 0) {
                return noComments.get(0);
            } else {
                // 根据 alter 语句
                // 剔除掉设置了备注信息的字段
                removeNoCommentColumnByAlters(noComments, alters);
                if (noComments.size() > 0) {
                    return noComments.get(0);
                } else {
                    return null;
                }
            }
        } else {
            return null;
        }
    }

    private void removeNoCommentColumnByAlters(List<ColumnDefinition> noComments, List<Alter> alters) {
        for (Alter alter : alters) {
            if (null != alter) {
                toRemoveByAlter(alter, noComments);
            }
        }
    }

    private void toRemoveByAlter(Alter alter, List<ColumnDefinition> noComments) {
        List<AlterExpression> expressions = alter.getAlterExpressions();
        if (null == expressions) {
            return;
        }
        for (AlterExpression exp : expressions) {
            String cname = exp.getColumnName();
            if (null == cname) {
                continue;
            }
            cname = cname.trim().toLowerCase();
            if (cname.length() <= 0) {
                continue;
            }
            String comment = exp.getCommentText();
            if (null == comment) {
                continue;
            }
            if (!valueHasReal(comment)) {
                continue;
            }
            removeByColumnName(noComments, cname);
        }
    }


    private List<Alter> altersAboutCreate(CreateTable create) {
        List<Statement> alters = state.getCare(CareSentence.ALTER);
        if (null == alters) {
            return Collections.emptyList();
        }
        int size = alters.size();
        if (size <= 0) {
            return Collections.emptyList();
        }
        final List<Alter> about = new ArrayList<>(size);
        for (int i = 0; i < size; i++) {
            Alter alter = (Alter) alters.get(i);
            if (null == alter) {
                continue;
            }
            if (isSame(alter, create)) {
                about.add(alter);
            }
        }
        return about;
    }

    private List<ColumnDefinition> noCommentInDefinitions(CreateTable create) {
        List<ColumnDefinition> definitions = create.getColumnDefinitions();
        int size = definitions.size();
        final List<ColumnDefinition> noComments = new ArrayList<>(size);
        for (int i = 0; i < size; i++) {
            ColumnDefinition definition = definitions.get(i);
            List<String> specs = definition.getColumnSpecs();
            boolean hasComment = columnHasComment(specs, definition);
            if (hasComment) {
                continue;
            } else {
                noComments.add(definition);
            }
        }
        return noComments;
    }

    private boolean columnHasComment(List<String> specs, ColumnDefinition column) {
        if (null == specs) {
            return false;
        }
        int size = specs.size();
        if (size <= 0) {
            return false;
        }
        for (int i = 0; i < size; i++) {
            String part = specs.get(i);
            if (null == part) {
                continue;
            }
            boolean has = hasCommentValue(i, size, specs, part);
            if (has) {
                return true;
            }
        }
        return false;
    }

    private boolean createHasPrimary(CreateTable create) {
        Object primary = primaryIndex(create);
        if (null == primary) {
            primary = primaryColumn(create);
        } else {
            return true;
        }
        if (null == primary) {
            primary = primaryAlter(create);
        } else {
            return true;
        }
        if (null == primary) {
            return false;
        } else {
            return true;
        }
    }

    private ColumnDefinition primaryColumn(CreateTable create) {
        List<ColumnDefinition> definitions = create.getColumnDefinitions();
        for (int i = definitions.size() - 1; i >= 0; i--) {
            ColumnDefinition definition = definitions.get(i);
            List<String> specs = definition.getColumnSpecs();
            if (null == specs) {
                continue;
            }
            if (primaryInSpecs(specs)) {
                return definition;
            }
        }
        return null;
    }

    private boolean primaryInSpecs(List<String> specs) {
        for (String sp : specs) {
            if (null == sp) {
                continue;
            }
            sp = sp.trim().toLowerCase();
            String word = SQLErrorType.NO_PRIMARY.getName();
            if (sp.contains(word)) {
                return true;
            }
        }
        return false;
    }

    private boolean likeOrAs(CreateTable create) {
        // maybe : create table t2 like t1;
        Table like = create.getLikeTable();
        // maybe : create table t2 as select * from t1;
        Select as = create.getSelect();
        if (null != like || null != as) {
            return true;
        } else {
            return false;
        }
    }

    // 不分 MySQL 或者 Oracle
    @Override
    public Map<SQLErrorType, Table> visitUpdate() {
        List<Statement> updates = state.getCare(CareSentence.UPDATE);
        if (null == updates) {
            return Collections.emptyMap();
        }
        int size = updates.size();
        if (size <= 0) {
            return Collections.emptyMap();
        }
        final Map<SQLErrorType, Table> map = new ConcurrentHashMap<>(8);
        for (int i = 0; i < size; i++) {
            Update update = (Update) updates.get(i);
            Expression where = update.getWhere();
            if (null == where) {
                Table table = update.getTable();
                if (null == table) {
                    table = new Table();
                }
                map.putIfAbsent(SQLErrorType.NO_WHERE, table);
                return map;
            }
        }
        return map;
    }


    private Alter primaryAlter(CreateTable create) {
        List<Statement> alters = state.getCare(CareSentence.ALTER);
        if (null == alters) {
            return null;
        } else {
            int size = alters.size();
            if (size <= 0) {
                return null;
            } else {
                return lookFromAlters(alters, size, create);
            }
        }
    }

    private Alter lookFromAlters(List<Statement> alters, int size, CreateTable create) {
        List<ColumnDefinition> definitions = create.getColumnDefinitions();
        for (int i = 0; i < size; i++) {
            Alter alter = (Alter) alters.get(i);
            if (!isSame(alter, create)) {
                continue;
            }
            List<AlterExpression> expressions = alter.getAlterExpressions();
            if (null == expressions) {
                continue;
            }
            if (visitExpression(expressions, definitions)) {
                return alter;
            }
        }
        return null;
    }

    private boolean visitExpression(List<AlterExpression> expressions, List<ColumnDefinition> definitions) {
        for (AlterExpression exp : expressions) {
            AlterOperation operation = exp.getOperation();
            if (null == operation) {
                continue;
            }
            if (operation != AlterOperation.ADD) {
                continue;
            }
            String info = exp.toString().toLowerCase();
            if (!info.contains(SQLErrorType.NO_PRIMARY.getName())) {
                continue;
            }
            List<String> pks = exp.getPkColumns();
            if (null == pks) {
                pks = pksFromAlterIndex(exp);
            }
            if (null == pks || pks.size() <= 0) {
                continue;
            }
            if (columnContains(definitions, pks)) {
                return true;
            }
        }
        return false;
    }

    private List<String> pksFromAlterIndex(AlterExpression exp) {
        Index index = exp.getIndex();
        if (null == index) {
            return Collections.emptyList();
        }
        String type = index.getType();
        if (null == type) {
            return Collections.emptyList();
        }
        type = type.toLowerCase();
        if (!type.contains(SQLErrorType.NO_PRIMARY.getName())) {
            return Collections.emptyList();
        }
        List<Index.ColumnParams> params = index.getColumns();
        if (null == params) {
            return Collections.emptyList();
        }
        final List<String> pks = new ArrayList<>(params.size());
        for (int i = params.size() - 1; i >= 0; i--) {
            Index.ColumnParams cp = params.get(i);
            String name = cp.columnName;
            if (null != name) {
                pks.add(name);
            }
        }
        return pks;
    }

    private boolean columnContains(List<ColumnDefinition> definitions, List<String> pks) {
        for (ColumnDefinition column : definitions) {
            String name = column.getColumnName();
            if (null == name) {
                continue;
            }
            name = cleanName(name);
            for (String pk : pks) {
                if (name.equals(pk.toLowerCase())) {
                    return true;
                }
            }
        }
        return false;
    }

    private String cleanName(String name) {
        String temp = name.trim().toLowerCase();
        boolean needClean = false;
        if (temp.startsWith("`")) {
            needClean = true;
        } else if (temp.startsWith("\"")) {
            needClean = true;
        } else if (temp.startsWith("\'")) {
            needClean = true;
        }
        if (needClean) {
            return temp.substring(1, temp.length() - 1);
        } else {
            return temp;
        }
    }

    private boolean isSame(Alter alter, CreateTable create) {
        Table a_table = alter.getTable();
        Table c_table = create.getTable();
        return sameTable(a_table, c_table);
    }

    private boolean isSame(Comment comment, CreateTable create) {
        Table a_table = comment.getTable();
        if (null == a_table) {
            Column column = comment.getColumn();
            if (null != column) {
                a_table = column.getTable();
            }
        }
        Table c_table = create.getTable();
        return sameTable(a_table, c_table);
    }

    private boolean sameTable(Table first, Table second) {
        if (null == first || null == second) {
            return false;
        } else {
            String fn = first.getName();
            String sn = second.getName();
            if (null == fn || null == sn) {
                return false;
            }
            fn = fn.trim().toLowerCase();
            sn = sn.trim().toLowerCase();
            if (fn.equals(sn)) {
                return true;
            } else {
                return false;
            }
        }
    }

    // 返回一个 index 信息
    private Index primaryIndex(CreateTable create) {
        if (null == create) {
            return null;
        }
        List<Index> indexs = create.getIndexes();
        if (null == indexs) {
            return null;
        } else {
            return lookIndexs(indexs);
        }
    }

    private Index lookIndexs(List<Index> indexs) {
        for (Index one : indexs) {
            String type = one.getType();
            if (null == type) {
                continue;
            } else {
                type = type.toLowerCase();
                String temp = SQLErrorType.NO_PRIMARY.getName();
                if (type.contains(temp)) {
                    return one;
                }
            }
        }
        return null;
    }

    private boolean hasCommentValue(int i, int size, List<String> specs, String part) {
        String temp = part.trim().toLowerCase();
        if (!temp.contains(SQLErrorType.NO_COMMENT.getName())) {
            return false;
        }
        if (i + 1 >= size) {
            return false;
        }
        // comment 字段后面的内容
        String info = specs.get(i + 1);
        if (null == info) {
            return false;
        }
        return valueHasReal(info);
    }

    private boolean valueHasReal(String info) {
        String copy = info.trim();
        if (copy.startsWith("`")) {
            if (copy.length() > 2) {
                return true;
            }
        } else if (copy.startsWith("\'")) {
            if (copy.length() > 2) {
                return true;
            }
        } else if (copy.startsWith("\"")) {
            if (copy.length() > 2) {
                return true;
            }
        } else {
            if (copy.length() > 0) {
                return true;
            }
        }
        return false;
    }

}
